﻿using Autofac;
using Dapper;
using MgSoft.Import.Exchange.ExWebApi.Db.Dtos;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace MgSoft.Import.Exchange.ExWebApi.Db.Daos
{
    public class SyncExtensionDao: ISyncExtensionDao
    {
        private string connString;

        public SyncExtensionDao(ILifetimeScope lifetimeScope, ConfigDto configDto)
        {
            connString = configDto.ConnString_Default;
        }

        public virtual void AddSyncData(SyncExtensionDto dto)
        {
            var addSqlString =
@"INSERT INTO [dbo].[SyncExtension]
           (
           [OrgNumber]
           ,[Biz_KeyField]
           ,[Biz_BillType]
           ,[Biz_BillTypeName]
           ,[FilterField])
     VALUES
           (
            @OrgNumber
           ,@Biz_KeyField
           ,@Biz_BillType
           ,@Biz_BillTypeName
           ,@FilterField)";
            using (var dbConnection = new SqlConnection(connString))
            {
                dbConnection.Execute(addSqlString, dto);
            }
        }

        public SyncExtensionDto Get(string keyFieldValue, string orgNumberValue, string Biz_BillType)
        {
            string sqlString = "SELECT * FROM [dbo].[SyncExtension] WHERE Biz_KeyField = @Biz_KeyField AND OrgNumber = @OrgNumber AND Biz_BillType=@Biz_BillType";
            using (var dbConnection = new SqlConnection(connString))
            {
                return dbConnection.Query<SyncExtensionDto>(sqlString, new { Biz_KeyField = keyFieldValue, OrgNumber = orgNumberValue, Biz_BillType = Biz_BillType }).FirstOrDefault();
            }
        }

        public SyncExtensionDto Get(string orgNumberValue, string Biz_BillType)
        {
            string sqlString = "SELECT * FROM [dbo].[SyncExtension] WHERE OrgNumber = @OrgNumber AND Biz_BillType=@Biz_BillType";
            using (var dbConnection = new SqlConnection(connString))
            {
                return dbConnection.Query<SyncExtensionDto>(sqlString, new { OrgNumber = orgNumberValue, Biz_BillType = Biz_BillType }).FirstOrDefault();
            }
        }

        public void UpdateFilterField(SyncExtensionDto dto)
        {
            var addSqlString = @"update [dbo].[SyncExtension] set [FilterField] = @FilterField WHERE OrgNumber = @OrgNumber AND Biz_BillType = @Biz_BillType ";
            using (var dbConnection = new SqlConnection(connString))
            {
                dbConnection.Execute(addSqlString, dto);
            }
        }
    }
}
